using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using emp.Model;
using emp.IDAL;
using System.Collections;
using emp.Utility;


namespace emp.SqlServerDAL
{
    public class DDR : IDDR
    {
        public void Insert(DDRInfo ddrinfo)
        {
            string SQL_INSERT_DDR = "INSERT INTO data_duplicate_rule(" +
            "ddr_source_id," +
            "ddr_name,"+
            "ddr_source_table,"+
            "ddr_source_key," +
            "ddr_source_field1," +
            "ddr_source_field2," +
            "ddr_source_field3," +
            "ddr_source_field4," +
            "ddr_source_field5," +
            "ddr_source_field6," +
            "ddr_source_field7," +
            "ddr_source_field8," +
            "ddr_source_field9," +
            "ddr_source_field10," +
            "ddr_destination_table," +
            "ddr_destination_key," +
            "ddr_destination_field1," +
            "ddr_destination_field2," +
            "ddr_destination_field3," +
            "ddr_destination_field4," +
            "ddr_destination_field5," +
            "ddr_destination_field6," +
            "ddr_destination_field7," +
            "ddr_destination_field8," +
            "ddr_destination_field9," +
            "ddr_destination_field10," +

            "ddr_self," +
            "ddr_type," +
            "ddr_isvalid,"+
            "ddr_batch_field,ddr_isvalid_field,ddr_dup_type,ddr_dup_con,ddr_seq)" +
            " VALUES("+
             "" + ddrinfo.DDR_Source_ID +
            ",'" + ddrinfo.DDR_Name +
            "','" + ddrinfo.DDR_Source_Table +
            "','" + ddrinfo.DDR_Source_Key+
            "','" + ddrinfo.DDR_Source_Field1 +
            "','" + ddrinfo.DDR_Source_Field2 +
            "','" + ddrinfo.DDR_Source_Field3 +
            "','" + ddrinfo.DDR_Source_Field4 +
            "','" + ddrinfo.DDR_Source_Field5 +
            "','" + ddrinfo.DDR_Source_Field6 +
            "','" + ddrinfo.DDR_Source_Field7 +
            "','" + ddrinfo.DDR_Source_Field8 +
            "','" + ddrinfo.DDR_Source_Field9 +
            "','" + ddrinfo.DDR_Source_Field10 +
            "','" + ddrinfo.DDR_Destination_Table +
            "','" + ddrinfo.DDR_Destination_Key +
            "','" + ddrinfo.DDR_Destination_Field1 +
            "','" + ddrinfo.DDR_Destination_Field2 +
            "','" + ddrinfo.DDR_Destination_Field3 +
            "','" + ddrinfo.DDR_Destination_Field4 +
            "','" + ddrinfo.DDR_Destination_Field5 +
            "','" + ddrinfo.DDR_Destination_Field6 +
            "','" + ddrinfo.DDR_Destination_Field7 +
            "','" + ddrinfo.DDR_Destination_Field8 +
            "','" + ddrinfo.DDR_Destination_Field9 +
            "','" + ddrinfo.DDR_Destination_Field10 +
            "','" + ddrinfo.DDR_Self +
            "','" + ddrinfo.DDR_Type +
            "','" + ddrinfo.DDR_IsValid +
            "','" + ddrinfo.DDR_Batch_Field +
            "','" + ddrinfo.DDR_IsValid_Field +
            "'," + ddrinfo.DDR_Dup_Type +
            ",'" + ddrinfo.DDR_Dup_Con +
            "',"+ddrinfo.DDR_Seq+")";

            //"," + contactinfo.Contact_DOid +

            SqlParameter[] Parms = new SqlParameter[] { };

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {

                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_DDR, Parms);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        System.Console.WriteLine(err.Message);
                    }
                }
            }
        }


        public void Update(DDRInfo ddrinfo)
        {
            string SQL_UPDATE_DDR = "update data_duplicate_rule set " +
                           "ddr_source_id='" + ddrinfo.DDR_Source_ID + "'," +
                           "ddr_batch_field='" + ddrinfo.DDR_Batch_Field + "'," +
                           "ddr_name='"+ddrinfo.DDR_Name+"'," +
                           "ddr_source_table='"+ddrinfo.DDR_Source_Table+"'," +
                           "ddr_source_key='"+ddrinfo.DDR_Source_Key+"'," +
                           "ddr_source_field1='"+ddrinfo.DDR_Source_Field1+"'," +
                           "ddr_source_field2='"+ddrinfo.DDR_Source_Field2+"'," +
                           "ddr_source_field3='"+ddrinfo.DDR_Source_Field3+"'," +
                           "ddr_source_field4='"+ddrinfo.DDR_Source_Field4+"'," +
                           "ddr_source_field5='" + ddrinfo.DDR_Source_Field5 + "'," +
                           "ddr_source_field6='" + ddrinfo.DDR_Source_Field6+ "'," +
                           "ddr_source_field7='" + ddrinfo.DDR_Source_Field7 + "'," +
                           "ddr_source_field8='" + ddrinfo.DDR_Source_Field8 + "'," +
                           "ddr_source_field9='" + ddrinfo.DDR_Source_Field9 + "'," +
                           "ddr_source_field10='" + ddrinfo.DDR_Source_Field10 + "'," +
                           "ddr_destination_table='" + ddrinfo.DDR_Destination_Table + "'," +
                           "ddr_destination_key='" + ddrinfo.DDR_Destination_Key + "'," +
                           "ddr_destination_field1='" + ddrinfo.DDR_Destination_Field1 + "'," +
                           "ddr_destination_field2='" + ddrinfo.DDR_Destination_Field2 + "'," +
                           "ddr_destination_field3='" + ddrinfo.DDR_Destination_Field3 + "'," +
                           "ddr_destination_field4='" + ddrinfo.DDR_Destination_Field4 + "'," +
                           "ddr_destination_field5='" + ddrinfo.DDR_Destination_Field5 + "'," +
                           "ddr_destination_field6='" + ddrinfo.DDR_Destination_Field6 + "'," +
                           "ddr_destination_field7='" + ddrinfo.DDR_Destination_Field7 + "'," +
                           "ddr_destination_field8='" + ddrinfo.DDR_Destination_Field8 + "'," +
                           "ddr_destination_field9='" + ddrinfo.DDR_Destination_Field9 + "'," +
                           "ddr_destination_field10='" + ddrinfo.DDR_Destination_Field10 + "'," +
                           "ddr_self=" + ddrinfo.DDR_Self + "," +
                           "ddr_type=" + ddrinfo.DDR_Type + ",ddr_isvalid_field='" + ddrinfo.DDR_IsValid_Field + "',ddr_dup_type=" + ddrinfo.DDR_Dup_Type + ",ddr_dup_con='" + ddrinfo.DDR_Dup_Con + "',ddr_seq=" + ddrinfo.DDR_Seq + " where ddr_id=" + ddrinfo.DDR_ID;

            //"," + contactinfo.Contact_DOid +

            SqlParameter[] Parms = new SqlParameter[] { };

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {

                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE_DDR, Parms);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        System.Console.WriteLine(err.Message);
                    }
                }
            }
        }

        public void Delete(int ddr_id)
        {
            string SQL_DELETE_DDR = "update data_duplicate_rule set" +
            " ddr_isvalid='0' where ddr_id=" + ddr_id;

            //"," + contactinfo.Contact_DOid +

            SqlParameter[] Parms = new SqlParameter[] { };

            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {

                        SQLHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_DELETE_DDR, Parms);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        System.Console.WriteLine(err.Message);
                    }
                }
            }
        }

        public IList getDDRList(int DDR_Source_ID)
        {
            string SQL_SELECT_DIT_LIST = "";
            if (DDR_Source_ID == 0)
            {
               SQL_SELECT_DIT_LIST= "SELECT DDR_ID,DDR_Source_ID,DDR_NAME,DDR_SOURCE_TABLE,DDR_SOURCE_KEY," +
                    "DDR_Source_Field1," +
                    "DDR_Source_Field2," +
                    "DDR_Source_Field3," +
                    "DDR_Source_Field4," +
                    "DDR_Source_Field5," +
                    "DDR_Source_Field6," +
                    "DDR_Source_Field7," +
                    "DDR_Source_Field8," +
                    "DDR_Source_Field9," +
                    "DDR_Source_Field10," +
                    "DDR_Destination_Table," +
                    "DDR_Destination_Key," +
                    "DDR_Destination_Field1," +
                    "DDR_Destination_Field2," +
                    "DDR_Destination_Field3," +
                    "DDR_Destination_Field4," +
                    "DDR_Destination_Field5," +
                    "DDR_Destination_Field6," +
                    "DDR_Destination_Field7," +
                    "DDR_Destination_Field8," +
                    "DDR_Destination_Field9," +
                    "DDR_Destination_Field10," +
                    "DDR_Self," +
                    "DDR_Type," +
                    "DDR_IsValid,DDR_Batch_Field,ddr_isvalid_field,ddr_dup_type,ddr_dup_con,ddr_seq from Data_Duplicate_Rule where ddr_isvalid='1'  and  DDR_ID>0 order by DDR_Source_ID asc,DDR_Seq asc";
            }
            else
            {
                SQL_SELECT_DIT_LIST = "SELECT DDR_ID,DDR_Source_ID,DDR_NAME,DDR_SOURCE_TABLE,DDR_SOURCE_KEY," +
                   "DDR_Source_Field1," +
                   "DDR_Source_Field2," +
                   "DDR_Source_Field3," +
                   "DDR_Source_Field4," +
                   "DDR_Source_Field5," +
                   "DDR_Source_Field6," +
                   "DDR_Source_Field7," +
                   "DDR_Source_Field8," +
                   "DDR_Source_Field9," +
                   "DDR_Source_Field10," +
                   "DDR_Destination_Table," +
                   "DDR_Destination_Key," +
                   "DDR_Destination_Field1," +
                   "DDR_Destination_Field2," +
                   "DDR_Destination_Field3," +
                   "DDR_Destination_Field4," +
                   "DDR_Destination_Field5," +
                   "DDR_Destination_Field6," +
                   "DDR_Destination_Field7," +
                   "DDR_Destination_Field8," +
                   "DDR_Destination_Field9," +
                   "DDR_Destination_Field10," +
                   "DDR_Self," +
                   "DDR_Type," +
                   "DDR_IsValid,DDR_Batch_Field,ddr_isvalid_field,ddr_dup_type,ddr_dup_con,ddr_seq from Data_Duplicate_Rule where ddr_isvalid='1' and DDR_Source_ID=" + DDR_Source_ID + " and  DDR_ID>0 order by DDR_Source_ID asc,DDR_Seq asc";
            }
            IList ddrlist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };
            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_DIT_LIST, parms))
            {
                while (rdr.Read())
                {
                    DDRInfo ddrinfo =
                         new DDRInfo(
                         rdr.GetInt32(0), 
                         rdr.GetInt32(1), 
                         rdr.GetString(2), 
                         rdr.GetString(3),
                         rdr.GetString(4),
                         rdr.GetString(5),
                         rdr.GetString(6),
                         rdr.GetString(7),
                         rdr.GetString(8),
                         rdr.GetString(9),
                         rdr.GetString(10),
                         rdr.GetString(11),
                         rdr.GetString(12),
                         rdr.GetString(13),
                         rdr.GetString(14),
                         rdr.GetString(15),
                         rdr.GetString(16),
                         rdr.GetString(17),
                         rdr.GetString(18),
                         rdr.GetString(19),
                         rdr.GetString(20),
                         rdr.GetString(21),
                         rdr.GetString(22),
                         rdr.GetString(23),
                         rdr.GetString(24),
                         rdr.GetString(25),
                         rdr.GetString(26),
                         rdr.GetInt32(27),
                         rdr.GetInt32(28),
                         rdr.GetString(29),
                         rdr.GetString(30),
                         rdr.GetString(31),
                         rdr.GetInt32(32),
                         rdr.GetString(33),
                         rdr.GetInt32(34));

                    ddrlist.Add(ddrinfo);
                }
            }
            return ddrlist;
        }

        public DDRInfo getDDRInfo(int DDR_ID)
        {
            string SQL_SELECT_DDR_LIST = "SELECT DDR_ID,DDR_Source_ID,DDR_NAME,DDR_SOURCE_TABLE,DDR_SOURCE_KEY," +
                "DDR_Source_Field1,"+
                "DDR_Source_Field2,"+
                "DDR_Source_Field3,"+
                "DDR_Source_Field4,"+
                "DDR_Source_Field5,"+
                "DDR_Source_Field6,"+
                "DDR_Source_Field7,"+
                "DDR_Source_Field8,"+
                "DDR_Source_Field9,"+
                "DDR_Source_Field10,"+
                "DDR_Destination_Table,"+
                "DDR_Destination_Key,"+
                "DDR_Destination_Field1,"+
                "DDR_Destination_Field2,"+
                "DDR_Destination_Field3,"+
                "DDR_Destination_Field4,"+
                "DDR_Destination_Field5,"+
                "DDR_Destination_Field6,"+
                "DDR_Destination_Field7,"+
                "DDR_Destination_Field8,"+
                "DDR_Destination_Field9,"+
                "DDR_Destination_Field10,"+
                "DDR_Self,"+
                "DDR_Type,"+
                "DDR_IsValid,DDR_Batch_Field,DDR_IsValid_Field,DDR_Dup_Type,DDR_Dup_Con,DDR_Seq from Data_Duplicate_Rule where ddr_id="+DDR_ID;

            DDRInfo ddrinfo = null;
            IList ddrlist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };
            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_DDR_LIST, parms))
            {
                while (rdr.Read())
                {
                   ddrinfo =
                         new DDRInfo(
                         rdr.GetInt32(0),
                         rdr.GetInt32(1),
                         rdr.GetString(2),
                         rdr.GetString(3),
                         rdr.GetString(4),
                         rdr.GetString(5),
                         rdr.GetString(6),
                         rdr.GetString(7),
                         rdr.GetString(8),
                         rdr.GetString(9),
                         rdr.GetString(10),
                         rdr.GetString(11),
                         rdr.GetString(12),
                         rdr.GetString(13),
                         rdr.GetString(14),
                         rdr.GetString(15),
                         rdr.GetString(16),
                         rdr.GetString(17),
                         rdr.GetString(18),
                         rdr.GetString(19),
                         rdr.GetString(20),
                         rdr.GetString(21),
                         rdr.GetString(22),
                         rdr.GetString(23),
                         rdr.GetString(24),
                         rdr.GetString(25),
                         rdr.GetString(26),
                         rdr.GetInt32(27),
                         rdr.GetInt32(28),
                         rdr.GetString(29),
                         rdr.GetString(30),
                         rdr.GetString(31),
                         rdr.GetInt32(32),
                         rdr.GetString(33),
                         rdr.GetInt32(34));

                }
            }
            return ddrinfo;
        }

        public int getSourceID(string sourcetable)
        {
            int ReturnValue = 0;
            string SQL_STR = "SELECT DDR_Source_ID FROM DATA_DUPLICATE_RULE WHERE DDR_Source_Table='"+sourcetable+"'";
            SqlParameter[] param = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_STR, param))
            {
                while (rdr.Read())
                {
                    ReturnValue = rdr.GetInt32(0);                                       
                }
            }

            return ReturnValue;            
        }


    }
}
